using System;
using SharpPieces.Web.Controls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Web;
using System.Security.Permissions;

namespace SharpPieces.DemoApp.LiveGrid
{

    /// <summary>
    /// LiveGrid Data Provider customization.
    /// </summary>
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level = AspNetHostingPermissionLevel.Minimal), AspNetHostingPermission(SecurityAction.InheritanceDemand, Level = AspNetHostingPermissionLevel.Minimal)]
    public class ThemesDataProvider : LiveGridDataProvider
    {

        // Methods

        /// <summary>
        /// Occurs when a grid needs a data source.
        /// </summary>
        /// <param name="offset">The row offset.</param>
        /// <param name="pageSize">The size of the page.</param>
        /// <param name="totalCount">The total count.</param>
        /// <returns>An IList, IEnumerable or IListSource.</returns>
        public override object NeedDataSource(int offset, int pageSize, out int totalCount)
        {
            // simple call to get data from DB

            DataTable tab = new DataTable();

            SqlConnection dbCon = null;
            try
            {
                dbCon = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["AdventureWorksConnStr"].ConnectionString);
                dbCon.Open();

                // bad practice but serves our purpose

                string sql =
                    "SELECT Name,EmailAddress,Phone  " + Environment.NewLine +
                    "FROM ( " + Environment.NewLine +
                    "    SELECT  ROW_NUMBER() OVER (ORDER BY fooOrder ASC) " + Environment.NewLine + 
                    "       AS Row,Name,EmailAddress,Phone " + Environment.NewLine +
                    "    FROM ( " + Environment.NewLine +
                    "        SELECT isnull(Title,'')+' '+isnull(FirstName,'')+' '+isnull(LastName,'') " + 
                        Environment.NewLine + 
                    "           Name,EmailAddress,Phone,fooOrder=getdate() from Person.Contact " + 
                        Environment.NewLine +
                    "        ) as personwithfooorder " + Environment.NewLine +
                    "    ) AS personwithrownumbers  " + Environment.NewLine +
                    "WHERE  Row >= " + offset.ToString() + " AND Row <= " + (offset + pageSize).ToString();

                SqlDataAdapter adapter = new SqlDataAdapter(sql, dbCon);
                adapter.Fill(tab);

                sql = "SELECT count(*) FROM Person.Contact";
                totalCount = (int)new SqlCommand(sql, dbCon).ExecuteScalar();
            }
            finally
            {
                if (null != dbCon)
                {
                    dbCon.Close();
                }
            }

            return tab;
        }

        /// <summary>
        /// Occurs when a grid needs a data source.
        /// </summary>
        /// <param name="offset">The row offset.</param>
        /// <param name="pageSize">The size of the page.</param>
        /// <param name="totalCount">The total count.</param>
        /// <param name="sortExpression">The sort expression.</param>
        /// <param name="sortDirection">The sort direction.</param>
        /// <returns>An IList, IEnumerable or IListSource.</returns>
        public override object NeedDataSource(int offset, int pageSize, out int totalCount, 
            string sortExpression, SortDirection sortDirection)
        {
            // simple call to get data from DB

            DataTable tab = new DataTable();

            SqlConnection dbCon = null;
            try
            {
                dbCon = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["AdventureWorksConnStr"].ConnectionString);
                dbCon.Open();

                // bad practice but serves our purpose

                string sql =
                    "SELECT  Name,EmailAddress,Phone " + Environment.NewLine +
                    "FROM ( " + Environment.NewLine +
                    "    SELECT  ROW_NUMBER() OVER (ORDER BY " + 
                        sortExpression + " " + ((sortDirection == SortDirection.Descending) ? "DESC" : "ASC") + 
                        ") AS Row,isnull(Title,'')+' '+isnull(FirstName,'')+' '+isnull(LastName,'') " + 
                        Environment.NewLine + " Name,EmailAddress,Phone  " + Environment.NewLine +
                    "    FROM Person.Contact " + Environment.NewLine +
                    "    ) AS personwithrownumbers  " + Environment.NewLine +
                    "WHERE  Row >= " + offset.ToString() + " AND Row <= " + (offset + pageSize).ToString();

                SqlDataAdapter adapter = new SqlDataAdapter(sql, dbCon);
                adapter.Fill(tab);

                sql = "SELECT count(*) FROM Person.Contact";
                totalCount = (int)new SqlCommand(sql, dbCon).ExecuteScalar();
            }
            finally
            {
                if (null != dbCon)
                {
                    dbCon.Close();
                }
            }

            return tab;
        }

    }

}
